package cn.edu.suda.ada.database.parser;

import cn.edu.suda.ada.database.objects.Column;
import cn.edu.suda.ada.database.objects.Key;
import cn.edu.suda.ada.database.objects.Table;

import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * MsSql解析
 * 
 * @author JiangJun
 *
 */
public class MssqlDbParser extends DbParser {
	// TODO 高级操作
	@Override
	public String createTable(Table table) {
		return null;
	}

	@Override
	public String createColumn(Column<?> column) {
		return null;
	}

	@Override
	public String createKey(Key key) {
		return null;
	}

	@Override
	public String dropTable(String tableName) {
		return null;
	}

	@Override
	public String exist(String tableName) {
		return null;
	}

	@Override
	public String showTable(String tableName, String databaseName) {
		return null;
	}

	@Override
	public Table mapToTable(Map<String, Object> map) {
		return null;
	}

	@Override
	public String showColumns(String tableName) {
		return null;
	}

	@Override
	public Column<?> mapToColumn(Map<String, Object> map) {
		return null;
	}

	@Override
	public String showKeys(String tableName) {
		return null;
	}

	@Override
	public Key mapToKey(Map<String, Object> map) {
		return null;
	}

	@Override
	public String alterColumn(String tableName, Column<?> oldColumn, Column<?> newColumn) {
		return null;
	}

	@Override
	public String dropColumn(String tableName, String columnName) {
		return null;
	}

	@Override
	public String addColumn(String tableName, Column<?> column) {
		return null;
	}

	@Override
	public String copyTableStructure(String tableName, String newTableName) {
		return null;
	}

	@Override
	public String copyTable(String tableName, String newTableName) {
		return null;
	}

	/**
	 * 获得替代字符串
	 *
	 * @param option
	 * @return
	 */
	public String getReplacement(OPTIONS option) {
		String replacement = option.toString();
		switch(option) {
			case ORDER :
			case GROUP :
				replacement += " BY";
				break;
			case TABLE:
			case FIELD:
			case DATA:
			case LIMIT:
				replacement = "";
			default:
		}
		replacement = " " + replacement + " ";
		return replacement;
	}

	@Override
	protected String parserSql(SQLTemplate sqlTemplate, Map<OPTIONS, Object> options) {
		sql = sqlTemplate.toString();
		Object obj = options.get(OPTIONS.LIMIT);
		if (null != obj) {
			String limit = obj.toString();
			if (limit.contains(OFFSET_KEYWORD)) {
				String[] tmp = limit.split(OFFSET_KEYWORD);
				sql = "SELECT top %LIMIT% %DISTINCT% %FIELD% FROM (SELECT row_number() over(%ORDER%) as rownumber,%DISTINCT% %FIELD% FROM %TABLE% %WHERE% %GROUP% %HAVING% %UNION%) A WHERE rownumber > %OFFSET%";
				sql = sql.replace("%LIMIT%", tmp[0].trim());
				sql = sql.replace("%OFFSET%", tmp[1].trim());
			} else {
				obj = options.get(OPTIONS.FIELD);
				if (null == obj) {
					options.put(OPTIONS.FIELD, "TOP " + limit + " *");
				} else {
					options.put(OPTIONS.FIELD, "TOP " + limit + " " + obj.toString());
				}
				options.remove(OPTIONS.LIMIT);
			}
		}
		for (OPTIONS option: options.keySet()) {
			String replace = getReplacement(option);
			Object object = options.get(option);
			if (null != object) {
				String value = object.toString();
				value = (null != value && !value.isEmpty()) ? replace + ((option.equals(OPTIONS.DISTINCT) ? '[' + value + ']' : value)) + " " : "";
				sql =  sql.replace("%" + option + "%", value);
			}
		}
		for (OPTIONS option: OPTIONS.values()) {
			sql =  sql.replace("%" + option + "%", "");
		}
		sql = sql.replaceAll("\\s+", " ");
		return sql;
	}

	private void where(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.WHERE);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> where = (Map<String, Object>) obj;
			if (!where.isEmpty()) {
				Object whereString = (String) where.get(WHERE_STRING);
				where.put(WHERE_STRING, null);
				List<String> whereList = new ArrayList<String>(where.size());
				if (null != whereString) {
					whereList.add(whereString.toString());
				}
				for (String key : where.keySet()) {
					Object value = where.get(key);
					if (null != value) {
						value = value.toString().replace("'", "\\'");
						whereList.add(String.format("%s = '%s'", key, value));
					}
				}
				options.put(OPTIONS.WHERE, StringUtils.join(whereList, " and "));
			}
		}
	}

	@Override
	public String select(Map<OPTIONS, Object> options) {
		// 一旦设置了DISTINCT，则FIELD失效
		if (null == options.get(OPTIONS.FIELD) && null == options.get(OPTIONS.DISTINCT)) {
			options.put(OPTIONS.FIELD, "*");
		}
		this.where(options);
		return parserSql(SQLTemplate.SELECT_SQL, options);
	}

	@Override
	public String count(Map<OPTIONS, Object> options, String field) {
		Object distinct = options.get(OPTIONS.DISTINCT);
		if (null == field || field.isEmpty() || "*".equals(field)) {
			Object tmp = options.get(OPTIONS.FIELD);
			if (null != tmp) {
				field = "[" + tmp.toString() + "]";
			} else {
				field = "*";
			}
		}
		if (null != distinct) {
			field = "DISTINCT([" + distinct.toString() + "])";
			options.remove(OPTIONS.DISTINCT);
		}
		field = " COUNT(" + field + ") ";
		options.put(OPTIONS.FIELD, field);
		options.put(OPTIONS.LIMIT, String.valueOf(1));
		return select(options);
	}

	@Override
	public String delete(Map<OPTIONS, Object> options) {
		this.where(options);
		return parserSql(SQLTemplate.DELETE_SQL, options);
	}

	private void insertData(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.DATA);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> data = (Map<String, Object>) obj;
			if (!data.isEmpty()) {
				List<String> fields = new ArrayList<String>();
				List<String> values = new ArrayList<String>();
				for(String field : data.keySet()){
					fields.add("[" + field + "]");
					Object val = data.get(field);
					String value = "null";
					if (null != val) {
						value = val.toString().replace("'", "\\'");
						value = "'" + value + "'";
					}
					values.add(value);
				}
				options.put(OPTIONS.FIELD, StringUtils.join(fields, ','));
				options.put(OPTIONS.DATA, StringUtils.join(values, ','));
			}
		}
	}
	
	@Override
	public String insert(Map<OPTIONS, Object> options) {
		this.insertData(options);
		
		this.where(options);
		return parserSql(SQLTemplate.INSERT_SQL, options);
	}

	@Override
	public String insert(Map<OPTIONS, Object> options, Map<String, Object> data) {
		if (null == data || data.isEmpty()){
			return null;
		}
		options.put(OPTIONS.DATA, data);
		return this.insert(options);
	}

	private void updateData(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.DATA);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> data = (Map<String, Object>) obj;
			if (!data.isEmpty()) {
				List<String> values = new ArrayList<String>();
				for(String field : data.keySet()){
					Object val = data.get(field);
					String value = "null";
					if (null != val) {
						value = val.toString().replace("'", "\\'");
						value = "'" + value + "'";
					}
					values.add(String.format("[%s] = %s", field, value));
				}
				
				options.put(OPTIONS.DATA, StringUtils.join(values.toArray(), ','));
			}
		}
	}
	
	
	@Override
	public String update(Map<OPTIONS, Object> options) {
		this.updateData(options);
		
		this.where(options);
		return parserSql(SQLTemplate.UPDATE_SQL, options);
	}

	@Override
	public String update(Map<OPTIONS, Object> options, Map<String, Object> data) {
		if (null == data || data.isEmpty()){
			return null;
		}
		options.put(OPTIONS.DATA, data);
		return this.update(options);
	}
}
